const { Grade, Student, Class, Teacher } = require('../models/db');
const XLSX = require('xlsx');

const ok = (res, data, extra = {}) => res.json({ success: true, data, ...extra });
const fail = (res, code = 500, message = '服务器错误') => res.status(code).json({ success: false, message, code });

const mapGradeToClient = (g) => ({
  id: g._id?.toString(),
  studentId: g.student ? g.student._id?.toString() || g.student?.toString() : undefined,
  studentName: g.student?.name,
  studentNumber: g.student?.studentId,
  classId: g.class ? g.class._id?.toString() || g.class?.toString() : undefined,
  className: g.class?.className,
  subject: g.subject,
  score: g.score,
  examType: g.examType,
  teachingMonth: g.teachingMonth,
  academy: g.academy,
  instructorId: g.instructor ? g.instructor._id?.toString() || g.instructor?.toString() : undefined,
  instructorName: g.instructor?.name,
  createdAt: g.createdAt ? new Date(g.createdAt).toISOString() : undefined,
  updatedAt: g.updatedAt ? new Date(g.updatedAt).toISOString() : undefined,
});

const mapClientToGrade = (body) => ({
  student: body.studentId,
  class: body.classId,
  subject: body.subject,
  score: body.score,
  examType: body.examType,
  teachingMonth: body.teachingMonth,
  academy: body.academy,
  instructor: body.instructorId,
  updatedAt: new Date(),
});

exports.getAllGrades = async (req, res) => {
  try {
    const { page = 1, limit = 20, studentId, classId, examType, subject } = req.query;
    const skip = (parseInt(page) - 1) * parseInt(limit);
    const query = {};
    
    if (studentId) query.student = studentId;
    if (classId) query.class = classId;
    if (examType) query.examType = examType;
    if (subject) query.subject = subject;
    
    const grades = await Grade.find(query)
      .populate('student', 'studentId name academy')
      .populate('class', 'className academy')
      .populate('instructor', 'name teacherId')
      .sort({ createdAt: -1 })
      .skip(skip)
      .limit(parseInt(limit));
    
    const total = await Grade.countDocuments(query);
    ok(res, grades.map(mapGradeToClient), {
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total,
        totalPages: Math.ceil(total / parseInt(limit))
      }
    });
  } catch (e) {
    fail(res, 500, e.message);
  }
};

exports.getGradeById = async (req, res) => {
  try {
    const g = await Grade.findById(req.params.id)
      .populate('student', 'studentId name academy')
      .populate('class', 'className academy')
      .populate('instructor', 'name teacherId phone');
    if (!g) return fail(res, 404, '未找到该成绩');
    ok(res, mapGradeToClient(g));
  } catch (e) {
    fail(res, 500, e.message);
  }
};

exports.createGrade = async (req, res) => {
  try {
    const payload = mapClientToGrade(req.body || {});
    if (!payload.student || !payload.class || !payload.subject || payload.score === undefined) {
      return fail(res, 400, '缺少必要字段: studentId | classId | subject | score');
    }
    
    // 填充学院信息
    const student = await Student.findById(payload.student);
    const classInfo = await Class.findById(payload.class);
    if (!student || !classInfo) {
      return fail(res, 400, '学生或班级不存在');
    }
    payload.academy = student.academy || classInfo.academy;
    
    const created = await Grade.create(payload);
    const g = await Grade.findById(created._id)
      .populate('student', 'studentId name')
      .populate('class', 'className')
      .populate('instructor', 'name');
    ok(res, mapGradeToClient(g));
  } catch (e) {
    fail(res, 500, e.message);
  }
};

exports.updateGrade = async (req, res) => {
  try {
    const payload = mapClientToGrade(req.body || {});
    payload.updatedAt = new Date();
    const updated = await Grade.findByIdAndUpdate(req.params.id, payload, { new: true })
      .populate('student', 'studentId name')
      .populate('class', 'className')
      .populate('instructor', 'name');
    if (!updated) return fail(res, 404, '未找到该成绩');
    ok(res, mapGradeToClient(updated));
  } catch (e) {
    fail(res, 500, e.message);
  }
};

exports.deleteGrade = async (req, res) => {
  try {
    const deleted = await Grade.findByIdAndDelete(req.params.id);
    if (!deleted) return fail(res, 404, '未找到该成绩');
    ok(res, { id: req.params.id });
  } catch (e) {
    fail(res, 500, e.message);
  }
};

// 获取班级成材率
exports.getClassSuccessRate = async (req, res) => {
  try {
    const { classId } = req.params;
    const successRate = await Grade.getClassSuccessRate(classId);
    ok(res, { classId, successRate: parseFloat(successRate) });
  } catch (e) {
    fail(res, 500, e.message);
  }
};

// 导出成绩模板
exports.exportGradeTemplate = async (req, res) => {
  try {
    const templateData = [
      ['学号', '姓名', '科目', '分数', '考试类型', '教学月']
    ];
    
    const workbook = XLSX.utils.book_new();
    const worksheet = XLSX.utils.aoa_to_sheet(templateData);
    XLSX.utils.book_append_sheet(workbook, worksheet, '成绩导入模板');
    
    // 将工作簿转换为缓冲区
    const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });
    
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', 'attachment; filename=成绩导入模板.xlsx');
    res.send(buffer);
  } catch (e) {
    fail(res, 500, e.message);
  }
};

// 导入成绩数据
exports.importGrades = async (req, res) => {
  try {
    if (!req.file) {
      return fail(res, 400, '请上传Excel文件');
    }
    
    const workbook = XLSX.read(req.file.buffer, { type: 'buffer' });
    const sheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[sheetName];
    const data = XLSX.utils.sheet_to_json(worksheet, { header: ['studentId', 'name', 'subject', 'score', 'examType', 'teachingMonth'] });
    
    const results = [];
    for (const row of data.slice(1)) { // 跳过表头
      try {
        const student = await Student.findOne({ studentId: row.studentId });
        if (!student) {
          results.push({ ...row, status: '失败', error: '学生不存在' });
          continue;
        }
        
        const gradeData = {
          student: student._id,
          class: student.class,
          subject: row.subject,
          score: parseFloat(row.score),
          examType: row.examType || '周考1',
          teachingMonth: row.teachingMonth,
          academy: student.academy
        };
        
        await Grade.create(gradeData);
        results.push({ ...row, status: '成功' });
      } catch (e) {
        results.push({ ...row, status: '失败', error: e.message });
      }
    }
    
    ok(res, { total: data.length - 1, results });
  } catch (e) {
    fail(res, 500, e.message);
  }
};

// 导出成绩数据
exports.exportGrades = async (req, res) => {
  try {
    const { classId, examType, startDate, endDate } = req.query;
    const query = {};
    
    if (classId) query.class = classId;
    if (examType) query.examType = examType;
    if (startDate || endDate) {
      query.createdAt = {};
      if (startDate) query.createdAt.$gte = new Date(startDate);
      if (endDate) query.createdAt.$lte = new Date(endDate);
    }
    
    const grades = await Grade.find(query)
      .populate('student', 'studentId name academy')
      .populate('class', 'className academy')
      .populate('instructor', 'name')
      .sort({ createdAt: -1 });
    
    const exportData = [
      ['序号', '学号', '姓名', '班级', '科目', '分数', '考试类型', '教学月', '学院', '任课教师', '录入时间']
    ];
    
    grades.forEach((g, index) => {
      exportData.push([
        index + 1,
        g.student?.studentId || '',
        g.student?.name || '',
        g.class?.className || '',
        g.subject,
        g.score,
        g.examType,
        g.teachingMonth || '',
        g.academy || '',
        g.instructor?.name || '',
        g.createdAt ? new Date(g.createdAt).toLocaleString('zh-CN') : ''
      ]);
    });
    
    const workbook = XLSX.utils.book_new();
    const worksheet = XLSX.utils.aoa_to_sheet(exportData);
    XLSX.utils.book_append_sheet(workbook, worksheet, '成绩数据');
    
    // 将工作簿转换为缓冲区
    const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });
    
    const fileName = `成绩数据_${new Date().toISOString().split('T')[0]}.xlsx`;
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename=${encodeURIComponent(fileName)}`);
    res.send(buffer);
  } catch (e) {
    fail(res, 500, e.message);
  }
};

